<?php
 
require_once(SERVER_FS_ROOT.'/app/_inc/sdao/MySqlAdapter.php');
require_once(SERVER_FS_ROOT.'/app/_inc/sdao/MySqliAdapter.php');
require_once(SERVER_FS_ROOT.'/app/_inc/sdao/DB2Adapter.php');

/**
 * Structured Data Access Object
 */
class SDAO {
	
	public $dbcPar; // data-base connection parameter (array)
	protected $dbcAdapter; // data-base connection adapter (object)
	protected $aQuery; // array di query
	protected $SQL; 
	public $errno, $error, $insertId, $affectedRows;
	
	public function __construct($dbcPar) {
		$this->dbcPar = $dbcPar;
		$this->dbcPar['dbcType'] = strtolower($dbcPar['dbcType']);
		$this->aQuery = array();
		$this->insertId = 0;
		$this->affectedRows = 0;
		$this->errno = 0;
		$this->error = '';
		$this->SQL = '';
		switch ($this->dbcPar['dbcType']) {
			case 'mysql' : 
				$this->dbcAdapter = new MySqlAdapter($dbcPar);
				break;
			case 'mysqli' : 
				$this->dbcAdapter = new MySqliAdapter($dbcPar);
				break;
			case 'db2' : 
				$this->dbcAdapter = new DB2Adapter($dbcPar);
				break;
			default: 
				$this->SQL = "DB Connection Type '$dbConnectionType' unknown";
				$this->logError(901,true);
		}
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		if ($this->errno != 0) {
			$this->logError(902,true);
		}
		
	}	

 /**
  * record the error_log for DB errors
  * @return bool
  */
 	function logError($errCode,$stopRun=false) {
		$idUser = (defined('USEROBJ') and isset($_SESSION[USEROBJ])) ? $_SESSION[USEROBJ]->id : 0;
		list($usec, $sec) = explode(" ", microtime());
		$tsNow = date('Y-m-d H:i:s',$sec).substr($usec,1,7); 
		$sSQL = "INSERT INTO error_log " .
				"(date_time_in, id_user, error_code, error_text) " .
				"VALUES(TIMESTAMP('$tsNow'), $idUser, ".
					intval($errCode).",'".
					addslashes(substr($this->errno.' - '.$this->error.' - '.$this->SQL,0,250))."')";	
 		$query = $this->dbcAdapter->query($sSQL, array("contOnErr"=>true));	
 		if ($stopRun) {
 			$this->stopOnError($errCode);
 		}
		return true;
 	}
 	function setError($errCode,$dbErrno,$dbError,$stopRun=false,$logError=true) {
 		$this->errno = $dbErrno;
		$this->error = $dbError;
		if ($logError) {
 			return $this->logError($errCode,$stopRun);
 		}
 		if ($stopRun) {
 			$this->stopOnError($errCode);
 		}
		return true;
 	}

 /**
  * write page db error and stop run !
  */
 	function stopOnError($errCode) {
 		// if http header already sent or it isn't an on-line process
 		if (headers_sent() or !isset($_SERVER["SERVER_NAME"])) {
 			$cred='';
 			foreach($this->dbcPar as $key => $val) {
				if ($key != 'password') {
					$cred .= "$key=$val,";
				}
			}	
	 		die("(SDAO$errCode) - ".$this->errno.' - '.$this->error.' - '.$this->SQL.' - cred.:'.$cred);
 		}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META Name="expires" CONTENT= "Mon, 1 Jan 2000 00:00:00 GMT">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache">
<META HTTP-EQUIV="content-language" content="it">
<META HTTP-EQUIV="Content-Type" content="text/html; charset=utf-8">
<TITLE>POLARIS - SQL error</TITLE>
<link rel="stylesheet" href="<?=SERVER_URL_ROOT?>/app/_com/stileMB.css.php" type="text/css" />
</HEAD>
<BODY>
  <table align='center' cellpadding='3'>
    <tr>
      <td colspan='2' class='titl01' valign='middle'>
      <img height='30' src="<?=SERVER_URL_ROOT?>/app/_img/attention.png" border='0' align='left'/>
      &nbsp;&nbsp;&nbsp;POLARIS - META-BASE<br/>
      &nbsp;&nbsp;&nbsp;SQL ERROR</td>
    </tr>
    <tr>
      <td class='labl01' width='100'>Error code</td><td class='text01' width='500'><?=$errCode?></td>
    </tr>
    <tr>
      <td class='labl01' width='100'>DB error code</td><td class='text01' width='500'><?=$this->errno?></td>
    </tr>
    <tr>
      <td class='labl01'>DB error</td><td class='text01'><?=$this->error?></td>
    </tr>
    <tr>
      <td class='labl01'>SQL</td><td class='text01'><?=$this->SQL?></td>
    </tr>
    <tr>
      <td class='labl01'>DB credential</td><td class='text01'>
<?php
foreach($this->dbcPar as $key => $val) {
	if ($key != 'password') {
		echo "$key = $val<br>";
	}
}
?>
		</td>
    </tr>
    <tr>
      <td colspan='2' align='right'>
<?
if (defined('USEROBJ') and isset($_SESSION[USEROBJ]) and is_object($_SESSION[USEROBJ])) {
?>
			<a href="<?=$_SESSION[USEROBJ]->getHistory(-1,true)?>" class='link01'>
				<?=_t('previus page')?>
			</a>
			&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<?
}
?>
			<a href="<?=SERVER_URL_ROOT?>/app/data/area/dataAreaList.php?xsn=<?=XSN?>" class='link01'>
				<?=_t('start page')?>
			</a>
			&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
			<a href="<?=SERVER_URL_ROOT?>/app/_com/logout.php?xsn=<?=XSN?>" class='link01'>
				<?=_t('log-out')?>
			</a>
      </td>
    </tr>
    
  </table> 
</BODY>
</HTML>
<?php
		exit();
 	}
 	
 /**
  * execute a SQL statement and store the created resource with an associated name 
  * @return bool
  */
 	function query($sql, $name='default', $aOpt='') {
 		if (is_array($sql)) {
 			if (isset($sql[$this->dbcPar['dbcType']])) {
 				$this->SQL = "SQL for ".$this->dbcType." not found. - ".print_r($sql,1);
 				$this->logError(903,true);
 			}
 			$sqlx = $sql[$this->dbcType];
 		} else {
 			$sqlx = $sql;
 		}
 		$this->SQL = $sqlx;
 		$query = $this->dbcAdapter->query($sqlx, $aOpt);	
 		$this->aQuery[$name] = $query;
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = $this->dbcAdapter->affectedRows;
		$this->insertId = $this->dbcAdapter->insertId;
		if ($this->errno == 0) return true;
		if(isset($aOpt["contOnErr"]) && $aOpt["contOnErr"]){
 			$this->logError(910);
			return false;
		}
 		$this->logError(911,true);
 		return false;
 	}
	
 /**
  * execute INSERT and log data 
  * @return bool
  */
 	function logInsert($entity, $aAttrib, $aKey, $alias='', $aOpt='') {
 		$sKeyLog = '';
 		$sep = '';
 		foreach($aKey as $nX => $kX) {
 			if (!isset($aAttrib[$kX])) {
 				return false;
 			}
 			$sKeyLog .= $sep.$vX;
 			$sep = '#';
 		}
		$aDiff = array();
		foreach($aData as $nX => $vX) {
			if ($vX != '') {
				$aDiff[$nX] = $vX;
			}
		}
		$sSql = "INSERT INTO data_log (date_time, login, tab_log, operation, diff) values ('"
			."'".date('Y-m-d H:i:s')."','".addslashes($objSess->login)."'"
			.",'$tab', 'I', '".addslashes($keyLog)."'"
			.", '".addslashes(gzcompress(serialize($aDiff)))."'";
		 		$this->exec($sSql);
 		$sSql = "INSERT INTO $tab (";
 		$sSql .= implode(', ',array_merge(array_keys($aKey),array_keys($aData)));
 		$sSql .= ") VALUES(";
 		$sComma= '';
 		foreach($aKey as $nX => $vX) {
 			$sSql .= $sComma."'".addslashes($vX)."'";
 			$sComma = ", ";
 		}
 		foreach($aData as $nX => $vX) {
 			$sSql .= $sComma."'".addslashes($vX)."'";
 			$sComma = ", ";
 		}
 		return $this->exec($sSql, $aOpt);	
 	}
	
 /**
  * execute UPDATE and log modified data 
  * @return bool
  */
 	function logUpdate($entity, $aAttrib, $aKey, $alias='', $aOpt='') {
 		global $objSess;
 		$sWhere = " WHERE ";
 		$sAnd = '';
 		$sKeyLog = '';
 		$sep = '';
 		foreach($aKey as $nX => $vX) {
 			$sWhere .= $sAnd." $nX = '".addslashes($vX)."' ";
 			$sAnd = "AND";
 			$sKeyLog .= $sep.$vX;
 			$sep = '#';
 		}
 		$this->SQL = "SELECT * FROM $tab ".$sWhere;
 		$query = $this->dbcAdapter->query($sSql, $aOpt);	
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		if ($this->errno != 0) {
			$this->logError(931,true);
 			return false;
		}
		if (!$row = $this->dbcAdapter->fetch($query)) {
			$this->setError(932,932,"Update key not found",true);
 			return false;
		}
		if ($row2 = $this->dbcAdapter->fetch($query)) {
			$this->setError(933,933,"Update key not unique",true);
 			return false;
		}
		$aDiff = array();
		foreach($aData as $nX => $vX) {
			if ($vX != $row[$nX]) {
				$aDiff[$nX] = array($row[$nX],$vX);
			}
		}
		if (count($aDiff)==0) return true;
		$sSql = "INSERT data_log (date_time, login, tab_log, operation, diff) values ('"
			."'".date('Y-m-d H:i:s')."','".addslashes($objSess->login)."'"
			.",'$tab', 'U', '".addslashes($keyLog)."'"
			.", '".addslashes(gzcompress(serialize($aDiff)))."'";
 		$this->exec($sSql);
 		$sSql = "UPDATE $tab SET ";
 		$sComma = ',';
 		foreach($aData as $nX => $vX) {
 			$sSql .= $sComma." $nX = '".addslashes($vX)."' ";
 			$sComma = ",";
 		}
 		$sSql .= $sWhere;
 		return $this->exec($sSql, $aOpt);	
 	}
	
 /**
  * execute DELETE and log deleted data 
  * @return bool
  */
 	function logDelete($entity, $aAttrib, $aKey, $alias='', $aOpt='') {
 		global $objSess;
 		$sWhere = " WHERE ";
 		$sAnd = '';
 		$sKeyLog = '';
 		$sep = '';
 		foreach($aKey as $nX => $vX) {
 			$sWhere .= $sAnd." $nX = '".addslashes($vX)."' ";
 			$sAnd = "AND";
 			$sKeyLog .= $sep.$vX;
 			$sep = '#';
 		}
 		$this->SQL = "SELECT * FROM $tab ".$sWhere;
 		$query = $this->dbcAdapter->query($sSql, $aOpt);	
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		if ($this->errno != 0) {
			$this->logError(931,true);
 			return false;
		}
		if (!$row = $this->dbcAdapter->fetch($query)) {
			$this->errno = 932;
			$this->error = "Update key not found";
			$this->logError(932,true);
 			return false;
		}
		if ($row2 = $this->dbcAdapter->fetch($query)) {
			$this->errno = 933;
			$this->error = "Update key not unique";
			$this->logError(933,true);
 			return false;
		}
		$aDiff = array();
		foreach($row as $nX => $vX) {
			if ($vX!='') {
				$aDiff[$nX] = $vX;
			}
		}
		if (count($aDiff)==0) return true;
		$sSql = "INSERT data_log (date_time, login, tab_log, operation, diff) values ('"
			."'".date('Y-m-d H:i:s')."','".addslashes($objSess->login)."'"
			.",'$tab', 'D', '".addslashes($keyLog)."'"
			.", '".addslashes(gzcompress(serialize($aDiff)))."'";
 		$this->exec($sSql);
 		$sSql = "DELETE FROM $tab ".$sWhere;
 		return $this->exec($sSql, $aOpt);	
 	}
 	
 	function fetch($name='default',$pos='') {
 		if (!isset($this->aQuery[$name])) {
 			return false;
 		}
 		$row = $this->dbcAdapter->fetch($this->aQuery[$name],$pos);	
 		return $row;
 	}
	
 	function free($name='default') {
 		if ($name == '*all') {
 			foreach($this->aQuery as $namex => $queryx) {
		 		$this->dbcAdapter->free($queryx);	
 			}
 			$this->aQuery = array();
 		}
 		if (!isset($this->aQuery[$name])) {
 			return false;
 		}
 		$this->dbcAdapter->free($this->aQuery[$name]);	
 		unset($this->aQuery[$name]);
 		return true;
 	}
	
 	function close() {
 		$this->dbcAdapter->close();	
 	}
 	
 /**
  * execute a SQL statement whitout create a resource (not select statement)
  * @return bool
  */
 	function exec($sql, $aOpt='') {
 		if (is_array($sql)) {
 			if (isset($sql[$this->dbcPar['dbcType']])) {
 				$this->SQL = "SQL for ".$this->dbcType." not found. - ".print_r($sql,1);
 				$this->logError(903,true);
			}
 			$sqlx = $sql[$this->dbcType];
 		} else {
 			$sqlx = $sql;
 		}
 		$this->SQL = $sqlx;
 		$query = $this->dbcAdapter->query($sqlx, $aOpt);	
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = $this->dbcAdapter->affectedRows;
		$this->insertId = $this->dbcAdapter->insertId;
		if ($this->errno == 0) {
			return true;
		}
		if(isset($aOpt["contOnErr"]) && $aOpt["contOnErr"]){
 			//$this->logError(912);
			return false;
		}
 		$this->logError(913,true);
 		return false;
 	}
	
 	function startTransaction() {
 		$this->dbcAdapter->startTransaction();	
 		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;	
 	}
 	
 	function commit() {
 		$this->dbcAdapter->commit();	
 		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
 	}
 	
 	function rollback() {
 		$this->dbcAdapter->rollback();	
 		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
 	}
 	
 	
 /**
  * Structured insert.
  * @return bool
  */
 	function insert($entity, $aAttrib, $aKey, $alias='', $aOpt='') {
 		$this->dbcAdapter->insert($entity, $aAttrib, $alias, $aOpt);	
 		$this->SQL = 'insert';
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = 0;
		$this->insertId = $this->dbcAdapter->insertId;
		if ($this->errno == 0) return true;
 		$this->logError(914,true);
 		return false;
 	}
	
 /**
  * Structured read.
  * @return bool
  */
 	function read($entity, &$aAttrib, $aKey, $alias='', $aOpt='') {
 		// $aOpt['lock'] = 'yes' : esegue select with lock
 		$rc = $this->dbcAdapter->read($entity, $aAttrib, $aKey, $alias, $aOpt);	
 		$this->SQL = 'read';
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = $this->dbcAdapter->affectedRows;
		$this->insertId = 0;
		if ($this->errno == 0) return $rc;
 		$this->logError(915,true);
 		return false;
 	}
 	
 /**
  * Structured update.
  * @return bool
  */
 	function update($entity, $aAttrib, $aKey, $alias='', $aOpt='') {
 		$this->dbcAdapter->update($entity, $aAttrib, $aKey, $alias, $aOpt);	
 		$this->SQL = 'update';
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = $this->dbcAdapter->affectedRows;
		$this->insertId = 0;
		if ($this->errno == 0) return true;
 		$this->logError(916,true);
 		return false;
 	}
 	
 /**
  * Structured delete.
  * @return bool
  */
 	function delete($entity, $aAttrib, $aKey, $alias='', $aOpt='') {
 		$this->dbcAdapter->delete($entity, $aAttrib, $aKey, $alias, $aOpt);	
 		$this->SQL = 'delete';
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = $this->dbcAdapter->affectedRows;
		$this->insertId = 0;
		if ($this->errno == 0) return true;
 		$this->logError(917,true);
 		return false;
 	}
 	
 /**
  * Returns an array of attribute-names of table.
  * fild_name => array properties
  * 	type => ...
  *   ???
  * @return array
  */
 	function getFields($entity, $aOpt='') {
 		$aFields = $this->dbcAdapter->getFields($entity);	
 		$this->SQL = 'getFields';
		$this->errno = $this->dbcAdapter->errno;
		$this->error = $this->dbcAdapter->error;
		$this->affectedRows = 0;
		$this->insertId = 0;
		if ($this->errno == 0) return $aFields;
 		$this->logError(918,true);
 	}
 	
 /**
  * Returns an associative array of attribute-names of table with prefix X_ - X = alias.
  * X_xxxx => '' 
  * @return array
  */
 	function getVarArray($entity, $alias, $aOpt='') {
 		$aFields =$this->getFields($entity);	
 		$aVar = array();
 		foreach($aFields as $name => $aProp) {
 			$aVar[$alias.'_'.$name] = '';
 		}	
 		return $aVar;
 	}

 /**
  * Returns an associative array of attribute-names of table with prefix X_ - X = alias.
  * the array is passed by reference and value  associated is not modified if it already exists 
  * X_xxxx => '' 
  * $aOpt : option
  * $aOpt['reset'] === true --> unset attribute with passed alias
  * @return array
  */
 	function addVarArray(&$aVar, $entity, $alias, $aOpt='') {
 		$aFields =$this->getFields($entity);	
 		if (isset($aOpt['reset']) and $aOpt['reset']===true) {
 			foreach($aVar as $name => $val) {
 				if ($name[0] == $alias) {
 					unset($aVar[$name]);
 				}
 			}
 		}
 		foreach($aFields as $name => $aProp) {
 			if (!isset($aVar[$alias.'_'.$name])) {
 				$aVar[$alias.'_'.$name] = '';
 			}
 		}	
 		return $aVar;
 	}

 /**
  * Returns a string with filds definition for SQL select statement.
  * the string is made in this way: X.xxxx AS X_xxxxx, X.yyyy AS X_yyyy
  * where X is alias and xxxx, yyyy are attribute-name 
  * @return string
  */
 	function getSelectVarString($entity, $alias, $aOpt='') {
 		$aFields =$this->getFields($entity);	
 		$sVar = '';
 		foreach($aFields as $name => $aProp) {
 			$sVar .= $alias.'.'.$name.' AS '.$alias.'_'.$name.', ';
 		}	
 		return substr($sVar,0,-2);
 	}

}
?>
